# —*— coding: utf-8 -*-

import os
from copy import deepcopy

# pip install openpyxl
from openpyxl import load_workbook
from openpyxl.workbook.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.chart._chart import ChartBase
from openpyxl.chart import (
    Reference,  # 引用
    BarChart,  # 条形图&柱状图
    BarChart3D,  # BarChart3D继承_3DBase
    LineChart,  # 折线图
    LineChart3D,  # 修改LineChart3D继承_3DBase
    PieChart,  # 饼图
    PieChart3D,  # 修改PieChart3D继承_3DBase
    AreaChart,
    AreaChart3D,  # 修改AreaChart3D继承_3DBase
)

from openpyxl.chart.title import Title
from openpyxl.chart.layout import Layout, ManualLayout
from openpyxl.chart.legend import Legend, LegendEntry
from openpyxl.chart.axis import TextAxis, NumericAxis, SeriesAxis, ChartLines
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.series import Series
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import (
    Font,
    Paragraph,
    ParagraphProperties,
    CharacterProperties,
    RichTextProperties
)

from openpyxl.styles import numbers

from py_excel import py_dir_path

from py_excel_draw import (
    draw_title,
    draw_layout,
    draw_legend,
    draw_series,
    draw_x_axis,
    draw_y_axis,
    draw_z_axis,
    draw_spPr,
    draw_txPr,
)


def insert_chart(chart: ChartBase, sheet: Worksheet, flag=False):
    """
    插入图表通用配置
    :param chart:
    :param sheet:
    :param flag
    :return:
    """
    # 位置
    sheet.add_chart(chart, 'H1')
    # 宽高
    chart.width = 8
    chart.height = 6

    # 类别（x轴）
    categories = Reference(sheet, min_col=1, max_col=2, min_row=2, max_row=sheet.max_row)
    chart.set_categories(categories)
    # 数据（y轴）
    data = Reference(sheet, min_col=2, max_col=sheet.max_column, min_row=1, max_row=sheet.max_row)
    chart.add_data(data, titles_from_data=True)
    # titles_from_data = True -> categories.min_row = data.min_row - 1

    if flag:
        draw_title(chart.title)
        draw_layout(chart.layout)
        draw_legend(chart.legend)
        draw_x_axis(chart.x_axis)
        draw_y_axis(chart.y_axis)

        series: Series = chart.series[0]
        draw_series(series)

        for series in chart.series:
            draw_series(sheet, series)
            pass

        # 风格一共有48种
        for i in range(1, 48, 1):
            temp = deepcopy(chart)
            temp.style = i
            sheet.add_chart(temp, 'H' + str(i * 10))
            pass
        pass

    pass


def insert_column_chart(sheet: Worksheet):
    """
    插入柱状图（垂直）
    :return:
    """
    bar_chart = BarChart()

    # bar_chart.type = 'bar'  # 条形图
    bar_chart.type = 'col'  # 柱状图

    bar_chart.grouping = 'stacked'
    bar_chart.overlap = 100

    insert_chart(bar_chart, sheet)

    pass


def insert_line_chart(sheet: Worksheet):
    """
    插入折线图
    :param sheet:
    :return:
    """
    line_chart = LineChart()
    insert_chart(line_chart, sheet)

    # x轴标签间隔自动
    line_chart.x_axis.auto = True

    pass


def insert_bar_chart(sheet: Worksheet):
    """
    插入条形图（水平）
    :param sheet:
    :return:
    """
    bar_chart = BarChart()
    bar_chart.type = 'bar'  # 条形图
    bar_chart.grouping = 'clustered'

    insert_chart(bar_chart, sheet)

    pass


def insert_pie_chart(sheet: Worksheet):
    """
    插入饼图
    :param sheet:
    :return:
    """
    pie_chart = PieChart()
    insert_chart(pie_chart, sheet)

    pass


def insert_3d_chart(sheet: Worksheet):
    """
    插入3D图标：以饼图为例
    :return:
    """
    pie_chart_3d = PieChart3D()
    insert_chart(pie_chart_3d, sheet)

    pass


if __name__ == '__main__':
    excel_path = os.path.join(py_dir_path, 'chart.xlsx')
    workbook: Workbook = load_workbook(excel_path)

    insert_column_chart(workbook['column'])
    # insert_line_chart(workbook['line'])
    insert_bar_chart(workbook['bar'])
    # insert_pie_chart(workbook['pie'])
    # insert_3d_chart(workbook['3d'])

    save_path = os.path.join(py_dir_path, 'save.xlsx')
    workbook.save(save_path)
    pass
